use entropy_bible_validation_VI, clear 
append using entropy_bible 

/* for the translation mya-x-bible-1835 some books have to be dropped,
because those books only contain ? */

drop if trans=="mya-x-bible-1835"&(book==9|book==10|book==14)     

/*keep only books with at least 100 available translations */
bysort book: gen available_books=_N
drop if available<100
tab book

/*get Spearman per book */

foreach type in order structure {
	bysort book: egen rank_`type'=rank(D_`type')
	bysort book: egen mean_`type'=mean(rank_`type')
	gen dev_`type'=(rank_`type'-mean_`type')^2
	bysort book: egen sum_`type'=sum(dev_`type')
}

gen cov=(rank_order-mean_order)*(rank_structure-mean_structure)
bysort book: egen sum_cov=sum(cov)

gen spearman=sum_cov/sqrt(sum_order*sum_structure)

/* get additional information (string length etc.) */

bysort book: egen p50_stringlength=pctile(chars_original), p(50)
bysort book: egen p25_stringlength=pctile(chars_original), p(25)
bysort book: egen p75_stringlength=pctile(chars_original), p(75)


/*generate table */
collapse spearman available_books p50 p25 p75, by(book)

/*book titles */

gen booktitle=""                                          
replace booktitle="Genesis" if book==1                    
replace booktitle="Exodus" if book==2                     
replace booktitle="Leviticus" if book==3                  
replace booktitle="Numbers" if book==4                    
replace booktitle="Deuteronomy" if book==5                
replace booktitle="Joshua" if book==6                     
replace booktitle="Judges" if book==7                     
replace booktitle="Ruth" if book==8                       
replace booktitle="1 Samuel" if book==9                   
replace booktitle="2 Samuel" if book==10                  
replace booktitle="1 Kings" if book==11                   
replace booktitle="2 Kings" if book==12                   
replace booktitle="1 Chronicles" if book==13              
replace booktitle="2 Chronicles" if book==14              
replace booktitle="Ezra" if book==15                      
replace booktitle="Nehemiah" if book==16                  
replace booktitle="Esther" if book==17                    
replace booktitle="Job" if book==18                       
replace booktitle="Psalms" if book==19                    
replace booktitle="Proverbs" if book==20                  
replace booktitle="Ecclesiastes" if book==21              
replace booktitle="Song of Solomon" if book==22           
replace booktitle="Isaiah" if book==23                    
replace booktitle="Jeremiah" if book==24                  
replace booktitle="Lamentations" if book==25              
replace booktitle="Ezekiel" if book==26                   
replace booktitle="Daniel" if book==27                    
replace booktitle="Hosea" if book==28                     
replace booktitle="Joel" if book==29                      
replace booktitle="Amos" if book==30                      
replace booktitle="Obadiah" if book==31                   
replace booktitle="Jonah" if book==32                     
replace booktitle="Micah" if book==33                     
replace booktitle="Nahum" if book==34                     
replace booktitle="Habakkuk" if book==35                  
replace booktitle="Zephaniah" if book==36                 
replace booktitle="Haggai" if book==37                    
replace booktitle="Zechariah" if book==38                 
replace booktitle="Malachi" if book==39                   
replace booktitle="Matthew"  if book==40                  
replace booktitle="Mark"  if book==41                     
replace booktitle="Luke"  if book==42                     
replace booktitle="John"  if book==43                     
replace booktitle="Acts"  if book==44                     
replace booktitle="Romans"  if book==45                   
replace booktitle="1 Corinthians"  if book==46            
replace booktitle="2 Corinthians"  if book==47            
replace booktitle="Galatians"  if book==48                
replace booktitle="Ephesians"  if book==49                
replace booktitle="Philippians"  if book==50              
replace booktitle="Colossians"  if book==51               
replace booktitle="1 Thessalonians"  if book==52          
replace booktitle="2 Thessalonians"  if book==53          
replace booktitle="1 Timothy"  if book==54                
replace booktitle="2 Timothy"  if book==55                
replace booktitle="Titus"  if book==56                    
replace booktitle="Philemon"  if book==57                 
replace booktitle="Hebrews"  if book==58                  
replace booktitle="James"  if book==59                    
replace booktitle="1 Peter"  if book==60                  
replace booktitle="2 Peter"  if book==61                  
replace booktitle="1 John"  if book==62                   
replace booktitle="2 John"  if book==63                   
replace booktitle="3 John"  if book==64                   
replace booktitle="Jude"  if book==65                     
replace booktitle="Revelation" if book==66                
replace booktitle="Tobit"                      if book==67
replace booktitle="Judith"                     if book==68
replace booktitle="Esther (Greek)"             if book==69
replace booktitle="Wisdom of Solomon"          if book==70
replace booktitle="Ecclesiasticus (Sirach)"    if book==71
replace booktitle="Baruch"                     if book==72
replace booktitle="Prayer of Azariah"          if book==74
replace booktitle="1 Maccabees"                if book==77
replace booktitle="2 Maccabees"                if book==78
replace booktitle="3 Maccabees"                if book==79
replace booktitle="4 Maccabees"                if book==80
replace booktitle="1 Esdras"                   if book==81
replace booktitle="2 Esdras"                   if book==82
replace booktitle="Prayer of Manasseh"         if book==83
replace booktitle="Psalm 151"                  if book==84
replace booktitle="Psalm of Solomon"           if book==85
replace booktitle="Odes"                       if book==86
replace booktitle="New Testament (aggr.)"                       if book==100
replace booktitle="Old Testament (aggr.)"                       if book==0
drop book

/*format data */
format %4.3f spearman
format %12.0fc avail p*


gsort -p50

qui sum spearman, d
di r(p25)
di r(p50)
di r(p75)

order book avail p* spearman
export excel using "correlation_table", sheetreplace firstrow(variables)
